﻿<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <meta name="apple-mobile-web-app-capable" content="yes" />
        <meta name="apple-mobile-web-app-status-bar-style" content="black" />
        <meta name="viewport" content="width=device-width, initial-scale=1, minimum-scale=1.0, maximum-scale=1.0, minimal-ui" />
        
        <title>Database API Documentation</title>
        
        <script src="/js/greyspots.js" type="text/javascript"></script>
        <link href="/css/greyspots.css" type="text/css" rel="stylesheet" />
        
        <script src="/js/ace/ace.js" data-ace-base="/js/ace/" type="text/javascript" charset="utf-8"></script>
        <script src="/js/ace/ext-language_tools.js" type="text/javascript"></script>
        <script src="/js/ace/ext-searchbox.js" type="text/javascript"></script>
        
        <script src="doc-library/doc.js" type="text/javascript"></script>
        <link href="doc-library/doc.css" type="text/css" rel="stylesheet" />
    </head>
    <body>
        <gs-jumbo>
            <h3>Database</h3>
        </gs-jumbo>
        <gs-container min-width="sml;med;lrg">
            <h3>URL:</h3>
            <pre><code>/env/[&lt;SCHEMA&gt;.]&lt;FUNCTION&gt;[?&lt;ARGS&gt;]</code></pre>
            
            <h3>Description:</h3>
            <p>These links handle running postgresql functions.</p>
            
            <p>Envelope provides context info to postgres for functions, access using:</p>
            <code>
            current_setting('sunny.context_info')
            </code>
            <p>This returns something like:</p>
            <pre>request_ip_address=127.0.0.1&request_host=127.0.0.1%3A8082&user_agent=Mozilla/5.0%20%28Windows%20NT%2010.0%3B%20Win64%3B%20x64%29%20AppleWebKit/537.36%20%28KHTML,%20like%20Gecko%29%20Chrome/57.0.2987.133%20Safari/537.36</pre>
            
            <h1>Examples:</h1>
            <div class="doc-example-description">
                <span class="h3">Action Example:</span><br/>
                <p>Action_ functions return json. Only functions that start with action_ (or accept_ like in the next example) are allowed.</p>
            </div>
            <gs-doc-example>
                <template for="html" height="5">
                    <gs-button onclick="ajax_example()">Action</gs-button>
                    <pre id="ajax_result"></pre>
                </template>
                <template for="js" height="20">
                    function ajax_example() {
                        GS.addLoader('loader', 'Ajax running...');
                        GS.ajaxJSON('/env/gsdoc.action_test', 'test=test'
                            , function (data, error) {
                            GS.removeLoader('loader');
                            
                            if (!error) {
                                document.getElementById('ajax_result').textContent = JSON.stringify(data);
                            } else {
                                GS.ajaxErrorDialog(data);
                            }
                        });
                    }
                </template>
                <template for="db" height="25">
                    CREATE OR REPLACE FUNCTION gsdoc.action_test(str_args text)
                     RETURNS text AS
                    $BODY$
                    DECLARE
                        str_test text;
                    BEGIN
                        --get the "test" parameter
                        str_test := gsdoc.getpar(str_args, 'test');
                        --jsonify the return variable
                        RETURN gsdoc.jsonify(str_test);
                        --try all of these
                        --RETURN '123';
                        --RETURN array_to_json(ARRAY[[str_args]]::text[][]);
                        --RETURN '"test"';
                        --RETURN '{"test": "test", "test2": 123}';
                    END; 
                    
                    $BODY$
                    LANGUAGE plpgsql VOLATILE
                    COST 100;
                    ALTER FUNCTION gsdoc.action_test(text) OWNER TO postgres;
                    GRANT ALL ON FUNCTION gsdoc.action_test(text) TO normal_g;
                    REVOKE ALL ON FUNCTION gsdoc.action_test(text) FROM public;
                </template>
            </gs-doc-example>
            
            <div class="doc-example-description">
                <span class="h3">Accept Example:</span><br/>
                <p>Accept_ functions return a full response.</p>
            </div>
            <gs-doc-example>
                <template for="html" height="5">
                    <gs-button onclick="ajax_example()">Accept</gs-button>
                    <pre id="ajax_result"></pre>
                </template>
                <template for="js" height="20">
                    function ajax_example() {
                        GS.addLoader('loader', 'Ajax running...');
                        GS.ajaxText('/env/gsdoc.accept_test', 'test=test'
                            , function (data, error) {
                            GS.removeLoader('loader');
                            
                            if (!error) {
                                document.getElementById('ajax_result').textContent = data;
                            } else {
                                GS.ajaxErrorDialog(data);
                            }
                        });
                    }
                </template>
                <template for="db" height="25">
                    CREATE OR REPLACE FUNCTION gsdoc.accept_test(str_args text)
                     RETURNS text AS
                    $BODY$
                    BEGIN
                        RETURN E'HTTP/1.1 200 OK\r\n' ||
                          E'Content-Type: text/csv\r\n\r\n' ||
                          E'Header1, Header2, Header3\r\n' ||
                          E'Data11, Data12, Data13\r\n' ||
                          E'Data21, Data22, Data23\r\n' ||
                          E'Data31, Data32, ' || str_args || '\r\n';
                    END; 
                    
                    $BODY$
                    LANGUAGE plpgsql VOLATILE
                    COST 100;
                    ALTER FUNCTION gsdoc.accept_test(text) OWNER TO postgres;
                    GRANT ALL ON FUNCTION gsdoc.accept_test(text) TO normal_g;
                    REVOKE ALL ON FUNCTION gsdoc.accept_test(text) FROM public;
                </template>
            </gs-doc-example>
            
            <div class="doc-example-description">
                <h3>Accept Download Example:</h3><br/>
                <p>When you want to download a file from the database, sometimes you will want a custom file name. Simply add the filename after the schema.function. That path will be sent to the function as path=/file.txt.</p>
            </div>
            <gs-doc-example>
                <template for="html">
                    <form id="form_example" method="post" action="/env/gsdoc.accept_test/file.txt">
                        <input type="hidden" name="test" value="test"/>
                    </form>
                    <gs-button onclick="document.getElementById('form_example').submit();">Accept</gs-button>
                </template>
                <template for="db" height="25">
                    CREATE OR REPLACE FUNCTION gsdoc.accept_test(str_args text)
                     RETURNS text AS
                    $BODY$
                    BEGIN
                        RETURN E'HTTP/1.1 200 OK\r\n' ||
                          E'Content-Type: text/csv\r\n\r\n' ||
                          E'Header1, Header2, Header3\r\n' ||
                          E'Data11, Data12, Data13\r\n' ||
                          E'Data21, Data22, Data23\r\n' ||
                          E'Data31, Data32, ' || str_args || '\r\n';
                    END; 
                    
                    $BODY$
                    LANGUAGE plpgsql VOLATILE
                    COST 100;
                    ALTER FUNCTION gsdoc.accept_test(text) OWNER TO postgres;
                    GRANT ALL ON FUNCTION gsdoc.accept_test(text) TO normal_g;
                    REVOKE ALL ON FUNCTION gsdoc.accept_test(text) FROM public;
                </template>
            </gs-doc-example>
        </gs-container>
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
        <br />
    </body>
</html>